﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace ERPTOOLS.Class
{
    public class DBLink
    {
        public static DataTable LinkERP(string sql, string ERPTABLE)
         {
            string strServer = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBIP");
            string strUID = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBUser");
            string strPwd = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBPassword");
            string strDB = XMLHelper.GetInnerTextByXpath("Configure.xml", "//Database");
            string strSQL = sql;
            string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
            DataTable myTable = getTable(str1, strSQL, ERPTABLE);
            return myTable;
         }

         public static DataTable LinkSCM(string sql,string SCMTABLE)
         {
             string strServer = "119.10.11.32";
             string strUID = "sa";
             string strPwd = "238173";
             string strDB = "SCMTEST";
             string strSQL = sql;
             string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
             DataTable myTable = getTable(str1, strSQL, SCMTABLE);
             return myTable;
         }

         private static DataTable getTable(string strCon, string strSql, string strTable)
         {
             try
             {
                 SqlConnection sqlcon = new SqlConnection(strCon);
                 SqlDataAdapter da = new SqlDataAdapter(strSql, sqlcon);
                 DataTable dt = new DataTable(strTable);
                 da.Fill(dt);
                 sqlcon.Close();
                 sqlcon.Dispose();
                 return dt;
             }
             catch
             {
                 return null;
             }
         }

         public static int LKSCM(string sql)
         {
             string strServer = "119.10.11.32";
             string strUID = "sa";
             string strPwd = "238173";
             string strDB = "SCMTEST";
             string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
             SqlConnection sqlcon = new SqlConnection(str1);
             sqlcon.Open();
             SqlCommand comd = new SqlCommand(sql, sqlcon);
             int count = comd.ExecuteNonQuery();
             sqlcon.Close();
             sqlcon.Dispose();
             return count;
         }

         public static int LKERP(string sql)
         {
             string strServer = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBIP");
             string strUID = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBUser");
             string strPwd = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBPassword");
             string strDB = XMLHelper.GetInnerTextByXpath("Configure.xml", "//Database");
             string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
             SqlConnection sqlcon = new SqlConnection(str1);
             sqlcon.Open();
             SqlCommand comd = new SqlCommand(sql, sqlcon);
             int count = comd.ExecuteNonQuery();
             sqlcon.Close();
             sqlcon.Dispose();
             return count;
        }
        public static DataTable TableaddSupCol(DataTable dt)
        {
           
                dt.Columns.Add("SUP");
                dt.Columns.Add("EMP");
                dt.Columns.Add("EML");
                foreach (DataRow row in dt.Rows)
                {
                    string sqlstr = "SELECT ME002 FROM SCMME WHERE ME001='"+ XMLHelper.GetInnerTextByXpath("Configure.xml", "//CompanyCode") + "'AND ME006='"+row["TB002"]+"'";
                    row["SUP"] = LKSCM_R(sqlstr);
                    sqlstr = "SELECT MG003 FROM SCMMG WHERE MG001 = '"+row["SUP"]+"' AND MG002 = '"+ XMLHelper.GetInnerTextByXpath("Configure.xml", "//CompanyCode") + "'";
                    row["EMP"] = LKSCM_R(sqlstr);
                    sqlstr = "SELECT MB006 FROM SYSMB WHERE MB001='"+row["SUP"]+"' AND MB002='"+row["EMP"]+"' AND MB007='Y'";
                    row["EML"]= LKSCM_R(sqlstr);
                }

            
                return dt;
        }

        public static DataTable TableaddSupCol_R(DataTable dt)
        {

            dt.Columns.Add("SUP");
            dt.Columns.Add("EMP");
            dt.Columns.Add("EML");
            foreach (DataRow row in dt.Rows)
            {
                string sqlstr = "SELECT ME002 FROM SCMME WHERE ME001='" + XMLHelper.GetInnerTextByXpath("Configure.xml", "//CompanyCode") + "'AND ME006='" + row["TE003"] + "'";
                row["SUP"] = LKSCM_R(sqlstr);
                sqlstr = "SELECT MG003 FROM SCMMG WHERE MG001 = '" + row["SUP"] + "' AND MG002 = '" + XMLHelper.GetInnerTextByXpath("Configure.xml", "//CompanyCode") + "'";
                row["EMP"] = LKSCM_R(sqlstr);
                sqlstr = "SELECT MB006 FROM SYSMB WHERE MB001='" + row["SUP"] + "' AND MB002='" + row["EMP"] + "' AND MB007='Y'";
                row["EML"] = LKSCM_R(sqlstr);
            }


            return dt;
        }

        public static string LKSCM_R(string sql)
        {
            string strServer = "119.10.11.32";
            string strUID = "sa";
            string strPwd = "238173";
            string strDB = "SCMTEST";
            string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
            SqlConnection sqlcon = new SqlConnection(str1);
            sqlcon.Open();
            SqlCommand comd = new SqlCommand(sql, sqlcon);
            string str = (String)comd.ExecuteScalar();
            sqlcon.Close();
            sqlcon.Dispose();
            return str;
        }

        public static string LKERP_R(string sql)
        {
            string strServer = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBIP");
            string strUID = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBUser");
            string strPwd = XMLHelper.GetInnerTextByXpath("Configure.xml", "//DBPassword");
            string strDB = XMLHelper.GetInnerTextByXpath("Configure.xml", "//Database");
            string str1 = "Data Source=" + strServer + ";database=" + strDB + ";Uid=" + strUID + ";Pwd=" + strPwd + ";";
            SqlConnection sqlcon = new SqlConnection(str1);
            sqlcon.Open();
            SqlCommand comd = new SqlCommand(sql, sqlcon);
            string str = (String)comd.ExecuteScalar();
            sqlcon.Close();
            sqlcon.Dispose();
            return str;
        }
    }

    
}
